package com.example.demo.mapper;

import com.example.demo.entity.Colleges;
import com.example.demo.entity.StuExternal;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StuExternalMapper {

    @Select("select * from stuexternal")
    List<StuExternal> selectAll();

    @Select("select max(englishTestFour) from stuexternal where id=#{id}")
    int selectMaxFour(int id);

    @Select("select * from stuexternal where id=#{id}")
    List<StuExternal> selectById(int id);

    @Select("select * from stuexternal where term=#{term}")
    List<StuExternal> selectByTerm(String term);

    @Select("select * from stuexternal where id=#{id} group by term")
    List<StuExternal> findByIdGroupByTerm(int id);

    @Select("select * from stuexternal where id=#{id} and term=#{term}")
   StuExternal selectByIdTerm(@Param("id") int id,@Param("term") String term);

    @Insert("insert into stuexternal(id,name,college,gredit,term,englishTestFour,computerLevel,credit) values (#{id},#{name},#{college},#{gredit},#{term},#{englishTestFour},#{computerLevel},#{credit})")
    int add(StuExternal stuExternal);

    @Update("update stuexternal set englishTestFour=#{englishTestFour},computerLevel=#{computerLevel} where id=#{id}")
    int update(@Param("id") int id, @Param("englishTestFour") int englishTestFour, @Param("computerLevel") String computerLevel);

    @Update("update stuexternal set englishTestFour=#{englishTestFour},computerLevel=#{computerLevel} where id=#{id} and term=#{term}")
    int updateMu(StuExternal stuFourDto);

    @Select("<script>select * from stuexternal <where>" +
            "<if test=\"graduationGredit!=null and graduationGredit!=''\">and gredit &gt;=#{graduationGredit}</if> "+
            " <if test=\"englishTestFour != null and englishTestFour!=''\">" +
            "and englishTestFour &gt;= #{englishTestFour}</if>" +
            " <if test=\"computerLevel != null and computerLevel != '' \" >" +
            "and computerLevel &gt;= #{computerLevel}</if>" +
            "<if test=\"totalCredit!=null and totalCredit!=''\">and credit &gt;=#{totalCredit}</if>  and term='end'"+
            "</where></script>")
    List<StuExternal> selectByNeed(Colleges colleges);

    @Select("<script>select * from stuexternal <where>" +
            "<if test=\"graduationGredit!=null and graduationGredit!=''\">and gredit &lt; #{graduationGredit}</if> "+
            " <if test=\"englishTestFour != null and englishTestFour!=''\">" +
            "and englishTestFour &lt; #{englishTestFour}</if>" +
            " <if test=\"computerLevel != null and computerLevel != '' \" >" +
            "and computerLevel &lt; #{computerLevel}</if>" +
            "<if test=\"totalCredit!=null and totalCredit!=''\">and credit &lt; #{totalCredit}</if>  and term='end'"+
            "</where></script>")
    List<StuExternal> selectByInNeed(Colleges colleges);
}
